package cloudnative.demo.labservice.mapper;

import cloudnative.demo.labservice.model.Manager;
import cloudnative.demo.labservice.model.Student;
import cloudnative.demo.labservice.model.Teacher;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface PersonnelMapper {
    // 查询所有教师
    @Select("SELECT * FROM teacher")
    List<Teacher> queryAllTeachers();

    // 按名字查询教师
    @Select("SELECT * FROM teacher WHERE name = #{name}")
    List<Teacher> findTeacherByName(@Param("name") String name);

    // 按ID查询教师
    @Select("SELECT * FROM teacher WHERE TeacherID = #{teacherId}")
    Teacher findTeacherById(@Param("teacherId") Long teacherId);

    // 按名字和ID查询教师
    @Select("SELECT * FROM teacher WHERE name = #{name} AND TeacherID = #{teacherId}")
    Teacher findTeacherByNameAndId(@Param("name") String name, @Param("teacherId") Long teacherId);

    // 插入新教师
    @Insert("INSERT INTO teacher (name, MobileNumber) VALUES (#{name}, #{mobileNumber})")
    void addTeacher(@Param("name") String name, @Param("mobileNumber") String mobileNumber);

    // 根据ID删除教师
    @Delete("DELETE FROM teacher WHERE TeacherID = #{teacherId}")
    void deleteTeacher(@Param("teacherId") Long teacherId);

    // 更新教师信息
    @Update("UPDATE teacher SET name = #{name}, MobileNumber = #{mobileNumber} WHERE TeacherID = #{teacherId}")
    void updateTeacher(@Param("teacherId") Long teacherId, @Param("name") String name, @Param("mobileNumber") String mobileNumber);

    @Select("SELECT * FROM student")
    List<Student> queryAllStudents();

    @Select("SELECT * FROM student WHERE name = #{name}")
    List<Student> findStudentByName(@Param("name") String name);

    @Select("SELECT * FROM student WHERE StudentID = #{studentId}")
    Student findStudentById(@Param("studentId") Long studentId);

    @Select("SELECT * FROM student WHERE name = #{name} AND StudentID = #{studentId}")
    Student findStudentByNameAndId(@Param("name") String name, @Param("studentId") Long studentId);

    @Insert("INSERT INTO student (name, MobileNumber) VALUES (#{name}, #{mobileNumber})")
    void addStudent(@Param("name") String name, @Param("mobileNumber") String mobileNumber);

    @Delete("DELETE FROM student WHERE StudentID = #{studentId}")
    void deleteStudent(@Param("studentId") Long studentId);

    @Update("UPDATE student SET name = #{name}, MobileNumber = #{mobileNumber} WHERE StudentID = #{studentId}")
    void updateStudent(@Param("studentId") Long studentId, @Param("name") String name, @Param("mobileNumber") String mobileNumber);


    @Select("SELECT * FROM manager")
    List<Manager> queryAllManagers();

    @Select("SELECT * FROM manager WHERE name = #{name}")
    List<Manager> findManagerByName(@Param("name") String name);

    @Select("SELECT * FROM manage WHERE WorkerID = #{workerId}")
    Manager findManagerById(@Param("workerId") Long workerId);

    @Select("SELECT * FROM manage WHERE name = #{name} AND WorkerID = #{workerId}")
    Manager findManagerByNameAndId(@Param("name") String name, @Param("workerId") Long workerId);

    @Insert("INSERT INTO manage (name, MobileNumber) VALUES (#{name}, #{mobileNumber})")
    void addManager(@Param("name") String name, @Param("mobileNumber") String mobileNumber);

    @Delete("DELETE FROM manage WHERE WorkerID = #{workerId}")
    void deleteManager(@Param("workerId") Long workerId);

    @Update("UPDATE manage SET name = #{name}, MobileNumber = #{mobileNumber} WHERE WorkerID = #{workerId}")
    void updateManager(@Param("workerId") Long workerId, @Param("name") String name, @Param("mobileNumber") String mobileNumber);


}
